package com.sdkj.dspservice.common.utils;

import com.sdkj.dspservice.common.config.ProjectConfig;
import com.sdkj.dspservice.modules.sys.entity.Annex;
import com.sdkj.dspservice.modules.sys.service.AnnexService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by 李晋 on 2016/12/5.
 * excel工具类
 */
public class ExcelUtils {

    private static ProjectConfig projectConfig = SpringContextHolder.getBean(ProjectConfig.class);
    private static AnnexService annexService = SpringContextHolder.getBean(AnnexService.class);

    private Workbook workbook;

    /**
     * 在已有模板中填充数据
     *
     * @param dataStartRow 数据行开始的行数
     * @param data         数据（map中  key为sheet名称  , value为数据）
     * @param haveBorder         是否有边框，默认没有
     */
    public ExcelUtils(Integer dataStartRow, Map<String, List<List<Object>>> data, boolean haveBorder) {
        workbook = new HSSFWorkbook();
        // 插入数据
        if (data != null && !data.isEmpty()) {
            // 表头变为淡蓝色
            CellStyle headCellStyle = workbook.createCellStyle();
            headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);

            // 表体
            CellStyle bodyCellStyle = workbook.createCellStyle();
            bodyCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            bodyCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            bodyCellStyle.setBorderTop(BorderStyle.THIN);//上边框
            bodyCellStyle.setBorderRight(BorderStyle.THIN);//右边框

            for (String key : data.keySet()) {
                Sheet sheet = workbook.getSheet(key);
                if (sheet == null) {
                    sheet = workbook.createSheet("sheet1");
                }
                List<List<Object>> dataSheet = data.get(key);
                int indexRow = dataStartRow - 1;
                for (List<Object> dataRow : dataSheet) {
                    // 在row中插入数据
                    Row row = sheet.getRow(indexRow);
                    if (row == null) {
                        row = sheet.createRow(indexRow);
                    }
                    int indexCol = 0;
                    for (Object dataCol : dataRow) {
                        // 在单元格中插入数据
                        Cell cell = row.getCell(indexCol);
                        if (cell == null) {
                            cell = row.createCell(indexCol);
                        }

                        if(indexRow == 0){
                            cell.setCellStyle(headCellStyle);
                        }
                        // 设置边框
                        if(haveBorder){
                            cell.setCellStyle(bodyCellStyle);
                        }
                        if (dataCol instanceof String) {
                            if(StringUtils.isNumber(String.valueOf(dataCol))){
                                cell.setCellValue(new BigDecimal(String.valueOf(dataCol)).doubleValue());
                            }else {
                                cell.setCellValue((String) dataCol);
                            }
                        } else if (dataCol instanceof BigDecimal || dataCol instanceof Double || dataCol instanceof Float) {
                            cell.setCellValue(((BigDecimal) dataCol).doubleValue());
                        } else if (dataCol instanceof BigDecimal || dataCol instanceof Double || dataCol instanceof Float) {
                            cell.setCellValue((Double) dataCol);
                        } else if (dataCol instanceof Boolean) {
                            cell.setCellValue((Boolean) dataCol);
                        } else if (dataCol instanceof Date) {
                            cell.setCellValue(DateUtils.formatDateTime((Date) dataCol));
                        }
                        ++indexCol;
                    }
                    ++indexRow;
                }
            }
        }
    }

    /**
     * 输出
     *
     * @param response
     * @param fileName
     */
    public void write(HttpServletResponse response, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }

    /**
     * 输出
     *
     */
    public void write(OutputStream outputStream) throws IOException {
        workbook.write(outputStream);
    }

    /**
     * 输出到附件
     * @param exportFileName    导出时的文件名
     * @throws IOException
     */
    public Annex writeAnnex(String exportFileName) throws IOException {
        // 生成文件
        String fileName = DateUtils.getDate("yyyyMMddHHmmssSSS") + UserUtils.getCurrentUserId();
        FileOutputStream fileOutputStream = new FileOutputStream(projectConfig.getUploadUrl() + File.separator + fileName + ".xls");
        write(fileOutputStream);
        // 保存到附件
        Annex annex = new Annex();
        annex.setUrl(projectConfig.getUploadUrl() + File.separator + fileName + ".xls");
        annex.setName(exportFileName);
        annexService.saveOrUpdate(annex);
        return annex;
    }

    /**
     * 复制工作表
     * 此方法主要用于复制2个不同HSSFWorkbook间的工作表
     */
    public static void copySheet(Workbook fromWorkbook, Workbook toWorkbook, String fromSheetName, String toSheetName) {
//        toWorkbook.setSheetName(toWorkbook.getSheetIndex(toSheetName), fromSheetName);
        Sheet fromSheet = fromWorkbook.getSheet(fromSheetName);
        for (int i = fromSheet.getFirstRowNum(); i <= fromSheet.getLastRowNum(); i++) {
            copyRows(fromWorkbook, toWorkbook, fromSheetName, toSheetName, i, i, i);
        }
    }

    /**
     * 复制行
     * 此方法主要用于复制2个不同Workbook间的行
     */
    public static void copyRows(Workbook fromWorkbook, Workbook toWorkbook, String fromSheetName, String toSheetName, int startRow, int endRow, int position) {
        Sheet fromSheet = fromWorkbook.getSheet(fromSheetName);
        Sheet toSheet = toWorkbook.getSheet(toSheetName);
        int i;
        int j;

        if ((startRow == -1) || (endRow == -1)) {
            return;
        }

        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (i = 0; i < fromSheet.getNumMergedRegions(); i++) {
            oldRanges.add(fromSheet.getMergedRegion(i));
        }

        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (CellRangeAddress oldRange : oldRanges) {
            CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
                    oldRange.getFirstColumn(), oldRange.getLastColumn());

            if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) {
                int targetRowFrom = oldRange.getFirstRow() - startRow + position;
                int targetRowTo = oldRange.getLastRow() - startRow + position;
                oldRange.setFirstRow(targetRowFrom);
                oldRange.setLastRow(targetRowTo);
                toSheet.addMergedRegion(oldRange);
                fromSheet.addMergedRegion(newRange);
            }
        }

        // 设置列宽
        for (i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
                    toSheet.setColumnHidden(j, false);
                }
                break;
            }
        }

        // 拷贝行并填充数据
        for (; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            Row toRow = toSheet.createRow(i - startRow + position);
            toRow.setHeight(fromRow.getHeight());
            for (j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) {
                Cell fromCell = fromRow.getCell(j);
                if (fromCell == null) {
                    continue;
                }
                Cell toCell = toRow.createCell(j);
                CellStyle toStyle = toWorkbook.createCellStyle();
                copyCellStyle(fromWorkbook, toWorkbook, fromCell.getCellStyle(), toStyle);
                toCell.setCellStyle(toStyle);
                CellType cType = fromCell.getCellType();
                switch (cType.ordinal()) {
                    case 4:
                        toCell.setCellValue(fromCell.getBooleanCellValue());
                        break;
                    case 5:
                        toCell.setCellErrorValue(fromCell.getErrorCellValue());
                        break;
                    case 2:
                        toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                        break;
                    case 0:
                        toCell.setCellValue(fromCell.getNumericCellValue());
                        break;
                    case 1:
                        toCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                }
            }
        }
    }

    /**
     * 复制行
     * 如果是同一个Workbook中的行请用此方法
     */
    public static void copyRows(Workbook workbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) {
        Sheet fromSheet = workbook.getSheetAt(fromSheetIndex);
        Sheet toSheet = workbook.getSheetAt(toSheetIndex);
        int i;
        int j;

        if ((startRow == -1) || (endRow == -1)) {
            return;
        }

        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (i = 0; i < fromSheet.getNumMergedRegions(); i++) {
            oldRanges.add(fromSheet.getMergedRegion(i));
        }

        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (CellRangeAddress oldRange : oldRanges) {
            CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
                    oldRange.getFirstColumn(), oldRange.getLastColumn());

            if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) {
                int targetRowFrom = oldRange.getFirstRow() - startRow + position;
                int targetRowTo = oldRange.getLastRow() - startRow + position;
                oldRange.setFirstRow(targetRowFrom);
                oldRange.setLastRow(targetRowTo);
                toSheet.addMergedRegion(oldRange);
                fromSheet.addMergedRegion(newRange);
            }
        }

        // 设置列宽
        for (i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
                    toSheet.setColumnHidden(j, false);
                }
                break;
            }
        }

        // 拷贝行并填充数据
        for (; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            Row toRow = toSheet.createRow(i - startRow + position);
            toRow.setHeight(fromRow.getHeight());
            for (j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) {
                Cell fromCell = fromRow.getCell(j);
                if (fromCell == null) {
                    continue;
                }
                Cell toCell = toRow.createCell(j);
                toCell.setCellStyle(fromCell.getCellStyle());
                CellType cType = fromCell.getCellType();
                switch (cType.ordinal()) {
                    case 4:
                        toCell.setCellValue(fromCell.getBooleanCellValue());
                        break;
                    case 5:
                        toCell.setCellErrorValue(fromCell.getErrorCellValue());
                        break;
                    case 2:
                        toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                        break;
                    case 0:
                        toCell.setCellValue(fromCell.getNumericCellValue());
                        break;
                    case 1:
                        toCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                }
            }
        }
    }

    /**
     * 复制单元格样式
     * 此方法主要用于复制2个不同Workbook间的单元格样式
     */
    public static void copyCellStyle(Workbook fromWorkbook, Workbook toWorkbook, CellStyle fromStyle, CellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignment());

        // 边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 字体
        Font tofont = toWorkbook.createFont();
            copyFont(((HSSFCellStyle) fromStyle).getFont(fromWorkbook), tofont);
        toStyle.setFont(tofont);

        // 背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        toStyle.setWrapText(fromStyle.getWrapText());
    }

    /**
     * 复制字体
     * 此方法主要用于复制2个不同Workbook间的字体
     */
    public static void copyFont(Font fromFont, Font toFont) {
        toFont.setCharSet(fromFont.getCharSet());
        toFont.setColor(fromFont.getColor());
        toFont.setFontHeight(fromFont.getFontHeight());
        toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints());
        toFont.setFontName(fromFont.getFontName());
        toFont.setItalic(fromFont.getItalic());
        toFont.setStrikeout(fromFont.getStrikeout());
        toFont.setTypeOffset(fromFont.getTypeOffset());
        toFont.setUnderline(fromFont.getUnderline());
    }

    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result;
        int index;

        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }

        return result.toString();
    }


    public static void copyCell(Cell srcCell, Cell distCell) {
        distCell.setCellStyle(srcCell.getCellStyle());
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        CellType srcCellType = srcCell.getCellType();
        if (srcCellType.ordinal() == 0) {
            if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType.ordinal() == 1) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType.ordinal() == 3) {
            // nothing21
        } else if (srcCellType.ordinal() == 4) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType.ordinal() == 5) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType.ordinal() == 2) {
            distCell.setCellFormula(srcCell.getCellFormula());
        } else {

        }
    }

}
